﻿
using CRL;
using QueryTest.Code;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;

namespace QueryTest.test
{
    public class select
    {
        public static void testSelect()
        {
            var time = DateTime.Now;
            var guid = new Guid();
            //select 测试
            var query = ProductDataRepository.Instance.GetLambdaQuery();
            //query.Where(b=> Convert.ToString(b.Id)=="11");
            query.Page(10, 1);
            //query.Where(b => b.id2 == guid);
            //query.Where(b => DateTime.Parse("2016-02-11 12:56") == b.AddTime);
            //query.Where(b => b.CategoryName != null);
            //query.Where(b => b.AddTime == time);
            var times = "2016-02-11 12:56";
            //query.Where(b => int.Parse(b.InterFaceUser) == 123);//支持Cast转换
            var result = query.Select(b => new
            {
                b.InterFaceUser,
                bb = b.Id * b.Number,
                bb2 = b.Id + 2,
                b.ProductName,
                id1 = Convert.ToString(b.Id),
                id2 = b.Id.ToString(),
                name2 = b.ProductName.Substring(0, 2),
                time = time,
                aa = DateTime.Parse("2016-02-11 12:56"),
                aa2 = Convert.ToDateTime(times),
                times = times,
                field = "field",
                ss = DataAccessType.Default,
                ss2 = true,
                ss3 = 5.23
            }).ToList();
            var sql = query.PrintQuery();

        }
        public static void testSelect2()
        {
            var query = OrderRepository.Instance.GetLambdaQuery();
            var item = query.ToSingle();
            var ss = item?.Status;
            query.PrintQuery();
        }
        public static void testBinarySelect()
        {
            var query = ProductDataRepository.Instance.GetLambdaQuery();
            query.Where(b => (b.Id + b.Number) * 3 < 110);
            query.Select(b => new
            {
                s1 = (b.Id + b.Number) * 3,
                s2 = b.Id * b.Number,
                s3 = b.Id
            }).ToSingle();
            var sql = query.PrintQuery();
        }
        public static void testPropertyValue()
        {
            var query = new MemberRepository().GetLambdaQuery().Take(5);
            var result = query.Select(b => b.Id).ToList();
            Console.WriteLine(result.Count);
        }
        public static void testPropertyValue2()
        {
            var query = new MemberRepository().GetLambdaQuery().Take(5);
            var result = query.Select(b => b.Id.SUM()).ToList();
            Console.WriteLine(result.Count);
        }
        public static void testFileldMapping()
        {
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery();
            query.Select(b => new { name2 = b.ProductName, ss2 = b.PurchasePrice * b.Id });
            query.Where(b => b.Id > 0);
            var result = query.ToDynamic();
            Console.WriteLine(query.ToString());
            foreach (var d in result)
            {
                var a = d.name2;
                var c = d.ss2;
            }
            var query3 = Code.ProductDataRepository.Instance.GetLambdaQuery();
            query3.Join<Code.Member>((a, b) => a.UserId == b.Id).SelectAppendValue((a, b) => b.Name);
            var resutl3 = query3.ToList();
            Console.WriteLine(query3.ToString());
            foreach (var d in resutl3)
            {
                var a = d.GetBag().Name;
                var a2 = d.GetIndexData("name");
            }
        }
        public static void testAnonymousResult()
        {
            var instance = MemberRepository.Instance;
            var result1 = instance.GetLambdaQuery().Select(b => new { b.Name }).ToList();
            var result2 = instance.GetLambdaQuery().Select(b => new Code.Member { Name = b.Name }).ToList();
            var query = instance.GetLambdaQuery();
            var join = query.Join<Order>((a, b) => a.Id == b.UserId);
            var result3 = join.Select((a, b) => new { a.Name, b.UserId }).ToList();
            var result4 = join.Select((a, b) => new anony1 { Name = a.Name, UserId = b.UserId }).ToList();
            query.PrintQuery();
        }
        public static void testAnonymousSelect()
        {
            var instance = MemberRepository.Instance;
            var query = instance.GetLambdaQuery();
            var join = query.Join<ProductData>((a, b) => a.Id == b.Id);
            var view = join.Select((a, b) => new { a.Id, b.InterFaceUser });
            view.OrderBy(b => b.InterFaceUser);
            view.OrderBy(b => b.Id);
            var result = view.ToList();
            query.PrintQuery();
        }

        public static void testSelftQuery3()
        {
            var query = MemberRepository.Instance.GetLambdaQuery();
            var v1 = query.GroupBy(b => b.AccountNo).Select(b => new { b.AccountNo, count = b.Id.COUNT() });

            var q2 = query.CreateQuery<Member>();
            var v2 = q2.Where(b => b.Name == "v2").GroupBy(b => b.AccountNo).Select(b => new { b.AccountNo, count = b.Id.COUNT() });
            var q3 = query.CreateQuery<Member>();
            var v3 = q3.Where(b => b.Name == "v3").GroupBy(b => b.AccountNo).Select(b => new { b.AccountNo, count = b.Id.COUNT() });
            var joinV = v2.Join(v3, (a, b) => a.AccountNo == b.AccountNo).Select((a, b) => new { a.AccountNo, count1 = a.count, count2 = b.count });

            var joinMain = v1.Join(joinV, (a, b) => a.AccountNo == b.AccountNo).Where((a, b) => b.count1 > 10 || b.count1 < 8).Select((a, b) => new { a.AccountNo, a.count });
            joinMain.PrintQuery();
            var result = joinMain.ToList();
        }
        public static void testCallBack()
        {
            var query = OrderRepository.Instance.GetLambdaQuery();
            query.Select(b => new { b.Id, b.OrderId });
            query.Take(10);
            query.Callback(r =>
            {
                var id = r["Id"];
                var orderId = r["OrderId"];
                Console.WriteLine($"id:{id} orderId:{orderId}");
            });
            query.PrintQuery();
        }
        public static void testFieldRef()
        {
            var instance = MemberRepository.Instance;
            var query = instance.GetLambdaQuery();
            var join = query.Join<Order>((a, b) => a.Id == b.UserId);
            var view = join.Select((a, b) => new { a.Name, b.UserId });
            var query2 = view.AsQuery();
            var view3 = query2.Select(b => new { a1 = b.Name, a2 = b.UserId });
            var result = view3.ToList();
            //var result4 = join.Select((a, b) => new anony1 { Name = a.Name, UserId = b.UserId }).ToList();
            //query.PrintQuery();
        }
        public static void testWindowFunc()
        {
            var query = OrderRepository.Instance.GetLambdaQuery();
            var windowFunc = query.CreateWindowFunc().Sum(b => b.Id).Over().PartitionBy(b => b.Remark).OrderBy(b => b.AddTime, true).OrderBy(b => b.ProductId, false);
            var v = query.Select(b => new { ss = windowFunc.End() });
            v.PrintQuery();
            var result = v.ToList();
        }
        public static void testConditionalExpression()
        {
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery();
            query.Where(b => b.Style == (b.Id > 1 ? "tt" : "dd"));
            var v = query.Select(b => new { a = b.Id > 1 ? "tt" : "dd" });
            query.PrintQuery();
            var list = v.ToList();
        }
        public static void testCase()
        {
            var query = Code.OrderRepository.Instance.GetLambdaQuery();
            var caseQuery = query.CreateCase().Case(x => x.Remark).When("1")//按字段值
                .Then(x => "11").Else(x => "22");
            query.Where(b => caseQuery.End() == "22");
            query.Select(b => new { ss = caseQuery.End() });
            query.PrintQuery();
            var result = query.ToList();
        }
        public static void testCase2()
        {
            var query = Code.OrderRepository.Instance.GetLambdaQuery();
            var caseQuery = query.CreateCase().When(b => b.Id > 1)//按条件
                .Then(x => "11").Else(x => "22");
            query.Where(b => caseQuery.End() == "22");
            query.Select(b => new { ss = caseQuery.End() });
            query.PrintQuery();
            var result = query.ToList();
        }

        public static void testCaseRow2Column()
        {
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery();
            var caseQuery = query.CreateCase("sum").Case(b => b.ProductChannel).When(1).Then(x => 1).Else(x => 0);
            var caseQuery2 = query.CreateCase("sum").Case(b => b.ProductChannel).When(0).Then(x => 1).Else(x => 0);
            query.GroupBy(b => b.ProductId).OrderBy(b => b.ProductId);
            query.Page(1, 1);
            var view = query.Select(b => new
            {
                b.ProductId,
                count1 = caseQuery.End(),
                count2 = caseQuery2.End()
            });

            query.PrintQuery();
            var result = view.ToList();
        }
        public static void testCaseView()
        {
            var query = OrderRepository.Instance.GetLambdaQuery();
            var query2 = query.CreateQuery<ProductData>();
            var view = query2.Select(b => new { name2 = b.CategoryName, b.InterFaceUser, b.ProductId });
            var caseQuery = view.CreateCase().Case(x => x.name2).When("1").Then(x => "11").Else(x => "22");
            var join = query.Join(view, (a, b) => a.OrderId == b.ProductId);
            join.Where((a, b) => caseQuery.End() == "22");
            var view2 = join.Select((a, b) => new { ss = caseQuery.End() });
            view2.PrintQuery();
            var result = view2.ToList();
        }
        public static void testSelectAsQuery()
        {
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery();
            query.Where(b => b.Id > 0);
            var query2 = query.GroupBy(b => new { b.ProductId, b.ProductChannel }).Select(b => new
            {
                b.ProductId,
                b.ProductChannel,
                count = b.ProductId.COUNT()
            }).AsQuery();

            var view3 = query2.GroupBy(b => b.ProductId).Select(b => new
            {
                b.ProductId,
                sum = b.count.SUM()
            });
            //query2.Page(1, 1).OrderBy(b => b.ProductId);
            query2.PrintQuery();
            var result = view3.ToList();
        }

        public static void testSp()
        {
            SettingConfig.CompileSp = true;
            var query = Code.OrderRepository.Instance.GetLambdaQuery();
            query.CompileToSp(true);
            var result = query.Where(b => b.Id == 1 && b.ProductId > 0).ToList();
        }
        public static void testSp2()
        {
            SettingConfig.CompileSp = true;
            var query = Code.OrderRepository.Instance.GetLambdaQuery();
            query.CompileToSp(true);
            var result = query.Where(b => b.Id > 0).Page(1, 1).ToList();
        }

    }
    public class anony1
    {
        public string Name
        {
            get; set;
        }
        public int UserId
        {
            get; set;
        }
    }
}
